﻿/*
Name:数据工具类
Author: Terry Dong
Description:用于处理数据库相关功能的辅助工具类
*/

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data.SqlClient;


namespace Gammar.Digger.SystemFramework
{
    public class DataUtility
    {
        /// <summary>
        /// 返回参数不为NULL的列表
        /// </summary>
        /// <param name="model"></param>
        /// <returns>返回dictionary，默认返回count=0的dic</returns>
        public static Dictionary<string, object> GetModelProperties(object model)
        {
            Dictionary<string, object> properties = new Dictionary<string, object>();

            //获得要添加的字段和值
            foreach (PropertyInfo p in model.GetType().GetProperties())
            {
                object value = p.GetValue(model, null);
                string key = p.Name;
                if (value != null)
                {
                    properties.Add(p.Name, value);
                }
            }
            return properties;
        }

        public static string GetPropertyName(object model, object props)
        {
            foreach (PropertyInfo p in model.GetType().GetProperties())
            {
                if (p.GetValue(model, null) == props)
                {
                    return p.Name;
                }
            }
            return props.ToString();
        }

        /// <summary>
        /// 生成添加记录sql
        /// </summary>
        /// <param name="model">参数不为Null的属性列表，GetModelProperties(object model)的返回值</param>
        /// <param name="tableName">表名</param>
        /// <param name="sql">返回sql语句</param>
        /// <returns>返回参数数组</returns>
        private static SqlParameter[] BuildAddSql(Dictionary<string, object> model, string tableName, ref string sql)
        {
            SqlParameter[] reval = null;
            StringBuilder strSql = new StringBuilder();
            if (model.Count > 0)
            {
                strSql.Append(string.Format("INSERT INTO {0}(", tableName));
                string fileds = string.Empty;
                string paraValue = string.Empty;
                reval = new SqlParameter[model.Count];

                int i = 0;
                foreach (KeyValuePair<string, object> keyValue in model)
                {

                    if (i == model.Count - 1)
                    {
                        fileds += keyValue.Key + ")";
                        paraValue += "@" + keyValue.Key + ")";
                    }
                    else
                    {
                        fileds += keyValue.Key + ",";
                        paraValue += "@" + keyValue.Key + ",";
                    }
                    reval[i] = new SqlParameter("@" + keyValue.Key, keyValue.Value);
                    i++;
                }
                strSql.Append(fileds);
                strSql.Append(" VALUES (");
                strSql.Append(paraValue);
            }
            sql = strSql.ToString();

            return reval;
        }

        private static SqlParameter[] BuildUpdateSql(Dictionary<string, object> model, string tableName, string where, ref string sql)
        {
            SqlParameter[] reval = null;
            StringBuilder strSql = new StringBuilder();
            if (model.Count > 0)
            {
                strSql.Append(string.Format("UPDATE {0} SET ", tableName));
                string fileds = string.Empty;
                string paraValue = string.Empty;
                reval = new SqlParameter[model.Count];

                int i = 0;
                foreach (KeyValuePair<string, object> keyValue in model)
                {

                    if (i == model.Count - 1)
                    {
                        strSql.Append(keyValue.Key + "=@" + keyValue.Key);
                    }
                    else
                    {
                        strSql.Append(keyValue.Key + "=@" + keyValue.Key + ",");
                    }
                    reval[i] = new SqlParameter("@" + keyValue.Key, keyValue.Value);
                    i++;
                }
                if (!string.IsNullOrEmpty(where))
                {
                    strSql.Append(" WHERE " + where);
                }
            }
            sql = strSql.ToString();

            return reval;
        }

        private static void BuildSelectSql(Dictionary<string, object> model, string tableName, string where, ref string sql)
        {
            // SqlParameter[] reval = null;
            StringBuilder strSql = new StringBuilder();
            if (model.Count > 0)
            {
                strSql.Append("SELECT ");
                string fileds = string.Empty;
                int i = 0;
                foreach (KeyValuePair<string, object> keyValue in model)
                {

                    if (i == model.Count - 1)
                    {
                        fileds += keyValue.Key + " ";
                    }
                    else
                    {
                        fileds += keyValue.Key + ",";
                    }
                    i++;
                }
                strSql.Append(fileds);
                strSql.Append(string.Format("FROM {0}", tableName));
                if (!string.IsNullOrEmpty(where))
                {
                    strSql.Append(" WHERE " + where);
                }
            }
            sql = strSql.ToString();
        }

        public static SqlParameter[] BuildAdd(object model, string tableName, ref string sql)
        {
            SqlParameter[] parameters = null;
            Dictionary<string, object> properties = GetModelProperties(model);

            if (properties.Count > 0)
            {
                parameters = BuildAddSql(properties, tableName, ref sql);
            }
            return parameters;
        }

        public static SqlParameter[] BuildUpdate(object model, string tableName, string where, ref string sql)
        {
            SqlParameter[] parameters = null;
            Dictionary<string, object> properties = GetModelProperties(model);

            if (properties.Count > 0)
            {
                parameters = BuildUpdateSql(properties, tableName, where, ref sql);
            }
            return parameters;
        }

        public static void BuildSelect(object model, string tableName, string where, ref string sql)
        {
            Dictionary<string, object> properties = GetModelProperties(model);

            if (properties.Count > 0)
            {
                BuildSelectSql(properties, tableName, where, ref sql);
            }
        }

        public static string BuildSelect(string[] selectFields, string tableName, string where)
        {
            string sql = string.Empty;

            if (selectFields.Length > 0)
            {
                StringBuilder strSql = new StringBuilder();

                strSql.Append("SELECT ");
                string fileds = string.Empty;
                int i = 0;
                foreach (string keyValue in selectFields)
                {

                    if (i == selectFields.Length - 1)
                    {
                        fileds += keyValue + " ";
                    }
                    else
                    {
                        fileds += keyValue + ",";
                    }
                    i++;
                }
                strSql.Append(fileds);
                strSql.Append(string.Format("FROM {0}", tableName));
                if (!string.IsNullOrEmpty(where))
                {
                    strSql.Append(" WHERE " + where);
                }
                sql = strSql.ToString();
            }
            return sql;

        }

    }
}
